package com.AddressListModule.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import util.ConnectDB;
import util.UtilDAO;

import com.AddressListModule.bean.TbUserDirectory;

public class TbUserDirectoryDAO {
	private Connection conn = null;
	private PreparedStatement pt = null;
	private Statement st = null;
	private ResultSet rs = null;
	private boolean flag = false;
	private String sql = "";
	
	private TbUserDirectory tbUserDirectory=null;
	private List<TbUserDirectory> tbUserDirectoryList=null;
	
	public boolean add(TbUserDirectory inTbUserDirectory){
		if(isExistTbUserDirectory(inTbUserDirectory))
			return true;
		
		try {
            conn = ConnectDB.getSqlServerConnection();
            sql = "insert into tb_UserDirectory(userId,directoryId) values(?,?)";
            pt = conn.prepareStatement(sql);
            pt.setInt(1, inTbUserDirectory.getUserId());
            pt.setInt(2, inTbUserDirectory.getDirectoryId());
            if (pt.executeUpdate() > 0) {
                flag = true;
            }
		}catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectDB.closeSqlConnection();
        }
		return flag;
	}
	public boolean del(int inUserDirectoryId){
		return UtilDAO.del("tb_UserDirectory", "user_directoryId", inUserDirectoryId);
	}
	public boolean delByUserId(int inUserId){
		return UtilDAO.del("tb_UserDirectory", "userId", inUserId);
	}
	public boolean delByDirectoryId(int inDirectoryId){
		return UtilDAO.del("tb_UserDirectory", "directoryId", inDirectoryId);
	}
	public boolean isExistTbUserDirectory(TbUserDirectory inTbUserDirectory){
		try {
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from tb_UserDirectory where userId=? And directoryId=?";
            pt = conn.prepareStatement(sql);
            pt.setInt(1, inTbUserDirectory.getUserId());
            pt.setInt(2, inTbUserDirectory.getDirectoryId());
            rs = pt.executeQuery();
            if(rs.next()) {
            	flag=true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
           ConnectDB.closeSqlConnection();
        }		
		return flag;			
	}
	
	public TbUserDirectory getTbUserDirectoryByUserDirectoryId(int inUserDirectoryId){
		try {
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from tb_UserDirectory where user_directoryId=?";
            pt = conn.prepareStatement(sql);
            pt.setInt(1, inUserDirectoryId);
            rs = pt.executeQuery();
            if(rs.next()) {
            	tbUserDirectory=getTbUserDirectoryInfo(rs);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
           ConnectDB.closeSqlConnection();
        }	
		return tbUserDirectory;
	}
	
	public List<TbUserDirectory> getTbUserDirectoryByUserId(int inUserId){
		try {
			tbUserDirectoryList=null;
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from tb_UserDirectory where userId=?";
            pt = conn.prepareStatement(sql);
            pt.setInt(1, inUserId);
            rs = pt.executeQuery();
            while(rs.next()) {
            	if(tbUserDirectoryList==null){
            		tbUserDirectoryList=new ArrayList<TbUserDirectory>();
            	}
            	tbUserDirectoryList.add(getTbUserDirectoryInfo(rs));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
           ConnectDB.closeSqlConnection();
        }	
		return tbUserDirectoryList;
	}
	public List<TbUserDirectory> getTbUserDirectoryByDirectoryId(int inDirectoryId){
		try {
			tbUserDirectoryList=null;
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from tb_UserDirectory where directoryId=?";
            pt = conn.prepareStatement(sql);
            pt.setInt(1, inDirectoryId);
            rs = pt.executeQuery();
            while(rs.next()) {
            	if(tbUserDirectoryList==null){
            		tbUserDirectoryList=new ArrayList<TbUserDirectory>();
            	}
            	tbUserDirectoryList.add(getTbUserDirectoryInfo(rs));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
           ConnectDB.closeSqlConnection();
        }	
		return tbUserDirectoryList;
	}
	
	public TbUserDirectory getTbUserDirectoryInfo(ResultSet rs) throws Exception{
		TbUserDirectory tbUserDirectory=new TbUserDirectory();
		tbUserDirectory.setUser_directoryId(rs.getInt("user_directoryId"));
		tbUserDirectory.setUserId(rs.getInt("userId"));
		tbUserDirectory.setDirectoryId(rs.getInt("directoryId"));
		tbUserDirectory.setCreateDate(rs.getString("createTime"));
		return tbUserDirectory;
		
	}
	/**
	 * 
	 *@author qingyu zhang
	 *@function:save list into the table of TbUserDirectory
	 * @param userDirList
	 * @return
	 *2011-3-3
	 *下午02:47:48
	 *UserModule
	 *com.AddressListModule.dao
	 *boolean
	 */
	public boolean saveUserDirList(List<TbUserDirectory> userDirList){
		boolean bo = false;
		try{
			 conn = ConnectDB.getSqlServerConnection();
			 st = conn.createStatement();
			 if(userDirList!=null&&userDirList.size()>0){
				 for(int v = 0;v<userDirList.size();v++){
					 String hql = "insert into tb_UserDirectory(userId,directoryId,createDate) " +
					 		"values("+userDirList.get(v).getUserId()+","+userDirList.get(v).getDirectoryId()+",'"+userDirList.get(v).getCreateDate()+"')";
					 st.addBatch(hql);
				 }
			 }
			 st.executeBatch();
			 bo = true;
		}catch(Exception e){
			e.printStackTrace();
		}finally {
           ConnectDB.closeSqlConnection();
           return bo;
        }	
	}
	
	public boolean deleteUserDir(int userId){
		boolean bo = false;
		try{
			 conn = ConnectDB.getSqlServerConnection();
			 sql = "delete from tb_userDirectory where userId="+userId;
			 pt = conn.prepareStatement(sql);
			 bo= pt.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally {
	           ConnectDB.closeSqlConnection();
	           return bo;
	        }	
	}
	

}
